﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DBS
{
	public class Requirements
	{
		public void ExportLaunch()
		{
			DataTable table;
			using (BaseDALC dalc = new BaseDALC())
			{
				table = dalc.Fill("select id from DBS$MON.req where rownum<10");
			}
			List<decimal> ids = new List<decimal>();
			foreach (DataRow row in table.Rows)
				ids.Add((decimal)row["id"]);
			ids.Add(2443225);
			ids.Add(2443081);
			ids.Add(2443306);
			Export(ids);
		}
		public void Export(List<decimal> ids)
		{
			DataTable reqs;
			DataTable debts;
			DataTable pds;
			using (BaseDALC dalc = new BaseDALC())
			{
				reqs = dalc.Fill(
					@"select req.*,
						case when specifying_req_sign = 1 then (select doc_num from DBS$MON.req req1 where req1.specifying_req_id = req.id) else null end spec_doc_num,
						case when specifying_req_sign = 1 then (select doc_date from DBS$MON.req req1 where req1.specifying_req_id = req.id) else null end spec_doc_date
						from DBS$MON.req
						where id in (" + string.Join(",", ids.ToArray()) + ")");
				debts = dalc.Fill(
					@"select dbt.*, nal.kod nalkbk, nal.naimd nalnaim, ok.kod okato_code
						from DBS$MON.req_debt dbt
						left join mdm_local.thes_snalog nal on dbt.tax_id = nal.record_id
						left join mdm_local.thes_okato ok on dbt.okato_id = ok.record_id
					where dbt.req_id in (" + string.Join(",", ids.ToArray()) + ")");
				pds = dalc.Fill(
					@"select pd.*, nal.kod nalkbk, ok.kod okato_code, tp.kod tp_code
						from DBS$MON.req_pd pd
						left join mdm_local.thes_snalog nal on pd.tax_id = nal.record_id
						left join mdm_local.thes_okato ok on pd.okato_id = ok.record_id
						left join dbs$erd.v$thes_ssprd tp on pd.tp_status_id = tp.record_id
					where pd.req_id in (" + string.Join(",", ids.ToArray()) + ")");
			}
			XMLExporter e = new XMLExporter(null);
			e.ElementCreate("ПАЧКА", "");
			e.Required = true;
			e.ANumber("Номер1", 3, 5678);
			e.ANumber("Номер2", 3, null);
			e.ANumber("Номер3", 3, DBNull.Value);
			foreach (DataRow req in reqs.Rows)
			{
				DataRow[] debt = debts.Select("req_id=" + req["id"].ToString());
				DataRow[] pd = pds.Select("req_id=" + req["id"].ToString());
				ExportDoc(e, req, debt, pd);
			}
			e.ElementClose("ПАЧКА");
			e.Write(@"..\..\..\ExportModule\sandbox\test.xml");

			foreach (string error in e.Errors)
				Console.WriteLine(error);
			Console.ReadLine();

		}
		private void ExportDoc(XMLExporter e, DataRow req, DataRow[] debt, DataRow[] pd)
		{
			e.Required = true;
			e.ElementCreate("ДОКУМЕНТ", "");
			e.ANumber("РегТреб", 10, req["id"]);
			e.ADate("ДатаДок", req["doc_date"]);
			e.ANumber("ИдНП", 10, req["tp_id"]);
			e.Required = false;
			e.ATextFix("ИНН", 12, req["inn"]);
			e.Required = true;
			string fio = req["name"].ToString();
			if (req["f_name"].ToString().Length > 0) fio += ", " + req["f_name"].ToString();
			if (req["s_name"].ToString().Length > 0) fio += ", " + req["s_name"].ToString();
			e.AText("ФИОФЛ", 1, 70, fio);
			e.Required = false;
			string[] adr = req["adres"].ToString().Split(',');
			if (adr.Length == 10)
			{
				e.ATextFix("п1", 6, adr[0]);
				e.ATextFix("п2", 2, "");
				e.ATextMax("п3", 50, adr[2]);
				e.ATextMax("п4", 50, adr[3]);
				e.ATextMax("п5", 50, adr[4]);
				e.ATextMax("п6", 50, adr[6]);
				e.ATextMax("п7", 15, adr[7]);
				e.ATextMax("п8", 15, adr[8]);
				e.ATextMax("п9", 15, adr[9]);
			}
			e.Required = true;
			e.ANumber("НомерТреб", 10, req["doc_num"]);
			e.ADate("ДатаСостоян", req["doc_date"]);
			e.ADate("Срок", req["performance_date"]);
			e.ACurrency("СуммаОбщ", req["doc_sum"]);
			e.ACurrency("СуммаНалог", req["debt_sum_tax"]);
			e.Required = false;
			e.ANumber("ПредНомер", 10, req["spec_doc_num"]);//не проверил. в базе данных нет
			e.ADate("ПредДата", req["spec_doc_date"]);
			e.AText("ФИОИсп", 1, 70, req["approver_user_fio"]);
			e.AText("Тел", 1, 10, "");
			e.Required = true;
			decimal sum1 = 0;
			decimal sum2 = 0;
			decimal sum3 = 0;
			foreach (DataRow row in debt)
			{
				if ((decimal)row["action_type_section_id"] == 1) sum1 += (decimal)row["ballance_sum"];
				if ((decimal)row["action_type_section_id"] == 2) sum2 += (decimal)row["ballance_sum"];
				if ((decimal)row["action_type_section_id"] == 3) sum3 += (decimal)row["ballance_sum"];
			}
			e.ACurrency("ИН", sum1);
			e.ACurrency("ИП", sum2);
			e.ACurrency("ИШ", sum3);

			int counter = 1;
			foreach (DataRow row in debt)
			{
				e.ElementCreate("ДОЛЖ", "");
				e.Required = true;
				e.ANumber("ПН", 5, counter); counter++;
				e.ATextFix("КБК", 20, row["nalkbk"]);
				e.AText("ВидНалога", 1, 100, row["nalnaim"]);
				e.ATextFix("ОКАТО", 11, row["okato_code"]);
				e.ADate("СрокУпл", row["init_action_date"]);

				if ((decimal)row["action_type_section_id"] == 1) sum1 = (decimal)row["ballance_sum"];
				if ((decimal)row["action_type_section_id"] == 2) sum2 = (decimal)row["ballance_sum"];
				if ((decimal)row["action_type_section_id"] == 3) sum3 = (decimal)row["ballance_sum"];
				e.ACurrency("Налог", sum1);
				e.ACurrency("Пеня", sum2);
				e.Required = false;
				e.ACurrency("Штраф", sum3);
				e.ElementClose("ДОЛЖ");
			}
			counter = 1;
			foreach (DataRow row in pd)
			{
				e.ElementCreate("ПД", "");
				e.Required = true;
				e.ATextFix("КБКПД", 20, row["nalkbk"]);
				string pdnum = row["pd_num"].ToString();
				if (pdnum.Length == 15)
					e.ATextFix("ИндДок", 15, row["pd_num"]);
				else
					e.ATextFix("ИндДок", 20, row["pd_num"]);
				e.AText("РегНо", 1, 10, row["id"]);
				e.ATextFix("ОКАТОПД", 11, row["okato_code"]);
				e.AText("ПолучПлат", 1, 200, row["print_pay_reciever_name"]);
				e.ATextFix("СчётПолуч", 20, row["bank_account_num"]);
				e.AText("НаимБанк", 1, 200, row["bank_name"]);
				e.ATextFix("БИК", 9, row["bank_bik"]);
				e.Required = false;
				e.AText("КоррСчет", 1, 20, row["bank_account_num"]);
				e.Required = true;
				e.ACurrency("НалогПД", row["pd_sum"]);
				e.ATextFix("ПД106", 2, row["print_pay_base"]);
				e.AText("ПД110", 1, 2, row["print_pay_type"]);
				e.Required = false;
				e.AText("Стат", 1, 20, "");
				e.Required = true;
				e.ATextFix("ПД101", 2, row["tp_code"]);
				e.ANumber("ПнПД", 5, counter); counter++;
				e.ElementClose("ПД");
			}

			e.ElementClose("ДОКУМЕНТ");

		}
	}
}
